IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PatientProcedureDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[PatientProcedureDetail]
GO


CREATE TABLE [dbo].[PatientProcedureDetail](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CRNumber] [bigint] NOT NULL,
	[Procedure] [bigint] NOT NULL,
	[ProcedureDate] [datetime] NOT NULL,
	[Remarks] [varchar](50) NULL,
	[Consultant] [bigint] NULL,
	[ReceiptId] [varchar](50) NULL,
	[Paid] [bit] NOT NULL
) ON [PRIMARY]
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.PatientProcedureDetail TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetPatientProcedureDetail') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetPatientProcedureDetail
GO
/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/18/2008
'SP Name    : dbo.SP_GetPatientProcedureDetail
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_GetPatientProcedureDetail]
@biCRN bigint
AS
   BEGIN
   SELECT
       PatientProcedureDetail.ID AS ID,
	   [Procedure].[Name] AS [ProcedureName],
	   PatientProcedureDetail.ProcedureDate AS Date,
	   Consultant.[Name] AS [ConsultantName],
	   PatientProcedureDetail.Paid AS Paid,
	   PatientProcedureDetail.ReceiptId AS ReceiptID,
	   PatientProcedureDetail.Remarks AS Remarks
   FROM (PatientProcedureDetail INNER JOIN [Procedure]
   ON PatientProcedureDetail.[Procedure] = [Procedure].ID) INNER JOIN Consultant
   ON PatientProcedureDetail.Consultant = Consultant.ID
   WHERE PatientProcedureDetail.CRNumber=@biCRN
   END



GO

GRANT EXEC ON dbo.SP_GetPatientProcedureDetail TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetPatientProcedureDetail') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetPatientProcedureDetail
GO
/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetPatientProcedureDetail
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_SetPatientProcedureDetail]

@biID    bigint,
@biCRN	 bigint,
@vchProcedure varchar(50),
@vchConsultant varchar(50),
@vchReceiptID varchar(50),
@vchRemarks varchar(50),
@bPaid bit,
@biOutID bigint OUTPUT,
@dtOutProcedureDate datetime OUTPUT
                    
AS
DECLARE  @iRetValue integer, 
	     @biConsultantID bigint,
		 @biProcedureID bigint

IF @vchConsultant IS NOT NULL
	SELECT @biConsultantID = ID FROM Consultant WHERE [Name]=@vchConsultant
IF @vchProcedure IS NOT NULL
	SELECT @biProcedureID = ID FROM [Procedure] WHERE [Name]=@vchProcedure

IF NOT EXISTS(SELECT     ID
   FROM PatientProcedureDetail
   WHERE   (ID = @biID)
   )

   BEGIN
   SET @dtOutProcedureDate = getdate()
   INSERT INTO PatientProcedureDetail(
       CRNumber,
	   [Procedure],
	   ProcedureDate,
	   Remarks,
	   Consultant,
	   ReceiptId,
	   Paid
  )
   VALUES (
       @biCRN,
	   @biProcedureID,
       @dtOutProcedureDate,
	   @vchRemarks,
	   @biConsultantID,
       @vchReceiptID,
	   @bPaid
   )
	SELECT @biID = @@Identity
   END
ELSE  IF EXISTS(SELECT     ID
   FROM PatientProcedureDetail
   WHERE   (ID = @biID)
   )

   BEGIN
   SELECT @dtOutProcedureDate = ProcedureDate FROM PatientProcedureDetail WHERE ID=@biID	
   UPDATE PatientProcedureDetail
   SET
       Remarks = @vchRemarks,
	   ReceiptId = @vchReceiptID
   WHERE (ID = @biID)
   END

IF (@@ERROR <> 0)
   BEGIN
	   declare @SPErrMsg1 varchar(800)
       SET @SPErrMsg1 = 'Error: Error in Stored procedure dbo.SP_SetProcedure for ID : ' + CAST (@biID as varchar) + ' in PatientProcedureDetail table.'
       RAISERROR (@SPErrMsg1,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
	   SET @biOutID = @biID
   END

RETURN @iRetValue



GO

GRANT EXEC ON dbo.SP_SetPatientProcedureDetail TO hms_usr
GO
